package fy.rest;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.logging.LogFactory;

import com.seeyon.ctp.util.UUIDLong;
import com.soa.eis.adapter.framework.message.IMsgObject;
import com.soa.eis.adapter.framework.message.impl.GroupRecord;
import com.soa.eis.adapter.framework.message.impl.MsgObject;
import com.soa.eis.adapter.framework.requester.IServiceRequester;
import com.soa.eis.adapter.framework.requester.impl.BaseServiceRequester;

import fy.config.FyConfig;
import fy.mdm.dao.MaterialRequestNoTimerTask;
import fy.uap.bean.ProviderImportFormBean;
import fy.uap.bean.RangeBean;
import fy.util.DBUtil;
import fy.util.MyUtil;

public class ProviderImportFormDao {
    private static org.apache.commons.logging.Log log = LogFactory
            .getLog(ProviderImportFormDao.class);
	// UPDATE_VENDOR_INVCLASS_STATE_CHANGE
    // insertVendorInvcalss
    // insertVendorInvcalssIfNotBlacklist

    private String uapUserID = "0001AA10000000009KX7";

    private ProviderImportFormBean providerImportFormBean;

    private List<String> companyList;

    private List<RangeBean> failRangeList;

    private List<RangeBean> unchangingRangeList;

    private List<RangeBean> successRangeList;

    private boolean writeToErpSuccess = false;

    private boolean writeAptitudeSuccess;

    public List<RangeBean> getFailRangeList() {
        return failRangeList;
    }

    public List<RangeBean> getUnchangingRangeList() {
        return unchangingRangeList;
    }

    public List<RangeBean> getSuccessRangeList() {
        return successRangeList;
    }

    public void setProviderImportFormBean(
            ProviderImportFormBean providerImportFormBean) {
        this.providerImportFormBean = providerImportFormBean;
    }

    public boolean save() {

        String UPDATE_VENDOR_INVCLASS_STATE_CHANGE = "update fy_supplier_vendor_invclass set cvendorclass=?, changeTrace=?, denddate=?,bankaccountname=?,bankcode = ?, bank = ?,bankaccount = ?,currency = ? where pk_veninv=? and cvendorclass<>'3'";

        String updateSuppliserBankInfo = "update fy_supplier_vendor_invclass i set i.bankaccountname=?,i.bankcode = ?, i.bank = ?,i.bankaccount = ?,i.currency = ? where i.pk_vendor = ( select v.pk_vendor from fy_supplier_vendor v where v.cvencode = ? and nvl(v.dr,0) = 0) and i.pk_corp=?";

        String insertSuppliserVendor = "insert into fy_supplier_vendor (pk_vendor,cvencode,cvenname,ctrade,cvenregcode,dorf,cqualitycond,cvenbank,cvenaccount,cmoneytype,cpurbillcode,couttype,cvenaddress,cvenpostcode, cvenperson, cvenphone,cvenfax,cvenemail,cveniaddress,ccuspaycond,ccontent,cycle,ccreateperson,dvendevdate, ts, creater,cVenBankMemo,cvenfygl) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        String updateSuppliserVendor = "update fy_supplier_vendor set cvenname=?,ctrade=?,cvenregcode=?,dorf=?,cqualitycond=?,cvenbank=?,cvenaccount=?,cmoneytype=?,cpurbillcode=?,couttype=?,cvenaddress=?,cvenpostcode=?, cvenperson=?, cvenphone=?,cvenfax=?,cvenemail=?,cveniaddress=?,ccuspaycond=?,ccontent=?,cycle=?,ccreateperson=?,dvendevdate=?, ts=?,cVenBankMemo=?,cvenfygl=? where cvencode=? and (dr='0' or dr is null) ";

        String insertVendorInvcalss = "insert into fy_supplier_vendor_invclass (pk_veninv, pk_corp, pk_invclass, cvendorclass, ts, pk_vendor, dvendevdate, changeTrace,creater,bankaccountname,bankcode, bank,bankaccount,currency) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?,?)";

        String SELECT_VENDOR_INVCLASS = "select t.pk_vendor, trim(t.pk_corp), t.pk_invclass, t.cvendorclass, t.pk_veninv,t.denddate from fy_supplier_vendor_invclass t, fy_supplier_vendor v where (t.dr='0' or t.dr is null) and v.pk_vendor = t.pk_vendor and v.cvencode = ? ";

        String insertVendorInvcalssIfNotBlacklist = "insert into fy_supplier_vendor_invclass (pk_veninv, pk_corp, pk_invclass, cvendorclass, ts, pk_vendor, dvendevdate, changeTrace, creater,bankaccountname,bankcode, bank,bankaccount,currency) select ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?,? from dual where not exists ( select * from fy_supplier_vendor_invclass where pk_corp=? and pk_invclass=? and pk_vendor=? and (dr='0' or dr is null) and ( cvendorclass = '3' or cvendorclass = '0'))";

        String insertLog = "insert into fy_supplier_vendor_history(pk_venhis, pk_vendor, pk_corp, pk_invclass, changetype, changememo, changevouch, changecode, changer, ts, changedate,creater) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";

        String insertLogIfNotBlacklist = "insert into fy_supplier_vendor_history(pk_venhis, pk_vendor, pk_corp, pk_invclass, changetype, changememo, changevouch, changecode, changer, ts, changedate, creater) select ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? from dual where not exists (select * from fy_supplier_vendor_invclass where pk_corp=? and pk_invclass=? and pk_vendor=? and (dr='0' or dr is null) and cvendorclass = '3')";

		// String ERPDELETESQL = "delete from apps.FYG_PO_UAP_VENDORS@"
        // + FyConfig.getERP_DB_CONFIG().getDblink()
        // + " i where i.VENDORS_NUM = ?  and i.ZT<>'客户' ";
        String ERPUADATE = "update apps.FYG_PO_UAP_VENDORS@"
                + FyConfig.getERP_DB_CONFIG().getDblink()
                + " av set (av.vendors_name, av.flag,av.zt,av.disable_date) = (select v.cvenname cvenname,      decode(sign(to_date(vi.denddate, 'YYYY-MM-DD') - sysdate),   -1,   'N',   decode(vi.cvendorclass, '3', 'N', 'Y')) flag   ,decode(sign(to_date(vi.denddate, 'YYYY-MM-DD') - sysdate),-1,'停用',   decode(vi.cvendorclass,          '1',          '合格',          '2',          '潜在',          '3',          '黑名单',          vi.cvendorclass)) zt,      to_date(vi.denddate, 'YYYY-MM-DD') disable_date from fy_supplier_vendor_invclass vi left join fy_supplier_vendor v on vi.pk_vendor = v.pk_vendor left join bd_corp c on vi.pk_corp = c.pk_corp left join fy_supplier_invclass i on vi.pk_invclass =       i.pk_invclass          where v.cvencode = ? and (vi.dr is null or vi.dr = 0) and av.vendors_num =v.cvencode and av.syb=c.unitname and av.uaplb = i.cinvclasscode )  where av.vendors_num =  ? and exists (select * from fy_supplier_vendor_invclass vi left join fy_supplier_vendor v on vi.pk_vendor = v.pk_vendor left join bd_corp c on vi.pk_corp = c.pk_corp left join fy_supplier_invclass i on vi.pk_invclass =       i.pk_invclass          where v.cvencode = ? and (vi.dr is null or vi.dr = 0) and av.vendors_num =v.cvencode and av.syb=c.unitname and av.uaplb = i.cinvclasscode) ";

        String ERPINSERTSQL = "insert into apps.FYG_PO_UAP_VENDORS@"
                + FyConfig.getERP_DB_CONFIG().getDblink()
                + "(VENDORS_NUM, VENDORS_NAME, SYB, UAPLB, ZT, disable_date,flag) select * from (select v.cvencode cvencode,  v.cvenname cvenname,  c.unitname unitname,  i.cinvclasscode cinvclasscode,  decode(sign(to_date(vi.denddate, 'YYYY-MM-DD') - sysdate),-1,'停用',decode(vi.cvendorclass,'1','合格',  '2',  '潜在',  '3',  '黑名单',  vi.cvendorclass)) zt,  to_date(vi.denddate, 'YYYY-MM-DD') disable_date,  decode(sign(to_date(vi.denddate, 'YYYY-MM-DD') - sysdate),-1,'N',decode(vi.cvendorclass,  '3',  'N',  'Y')) flag    from fy_supplier_vendor_invclass vi    left join fy_supplier_vendor v on vi.pk_vendor = v.pk_vendor    left join bd_corp c on vi.pk_corp = c.pk_corp    left join fy_supplier_invclass i on vi.pk_invclass = i.pk_invclass where v.cvencode=? and (vi.dr is null or vi.dr = 0) ) t where not exists ( select *    from apps.FYG_PO_UAP_VENDORS@"
                + FyConfig.getERP_DB_CONFIG().getDblink()
                + " e where t.cvencode = e.VENDORS_NUM and e.SYB = t.unitname and e.UAPLB=t.cinvclasscode and t.zt = e.zt and (t.disable_date is null and e.disable_date is null or t.disable_date = e.disable_date) ) ";

        boolean success = false;
        Connection conn = null;
        PreparedStatement pst = null;
        PreparedStatement pst1 = null;
        PreparedStatement pst2 = null;
        ResultSet rs = null;
        try {

			// FileOutputStream fos = new FileOutputStream("E:/1111");
            // fos.write(providerImportFormBean.printFields().getBytes());
            // fos.flush();
            // fos.close();
            conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());
            long changeTrace = UUIDLong.longUUID();
			// System.out.println(this.providerImportFormBean.printFields());

			// 新增加供应商的导入
            // OA 0001AA10000000009KX7
            // UUIDLong.longUUID()
            // 1001 单位
            // "insert into fy_supplier_vendor
            // (pk_vendor,cvencode,cvenname,ctrade,cvenregcode,
            // dorf,cqualitycond,cvenbank,cvenaccount,cmoneytype,
            // cpurbillcode,couttype,cvenaddress,cvenpostcode,
            // cvenperson, cvenphone,cvenfax,cvenemail,cveniaddress,
            // ccuspaycond,ccontent,cycle,ccreateperson,dvendevdate)
            // values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            //查询供应商名称
            pst = conn
                    .prepareStatement("select cvenname from fy_supplier_vendor where cvencode=? and  nvl(dr,0) = 0");

            pst.setString(1, providerImportFormBean.getProviderCode());
            rs = pst.executeQuery();
            int providerIsExist = -1;
            
//            判断供应商是否存在
            if (rs.next()) {
                if (providerImportFormBean.getProviderName().equals(
                        rs.getString(1))) {
//                	存在
                    providerIsExist = 1;
                }
            } else {
//            	不存在
                providerIsExist = 0;
            }

            
            
            conn.setAutoCommit(false);
            if (providerIsExist == 0) {
//供应商部存在、新增供应商
//            	插入主表数据
                pst = conn.prepareStatement(insertSuppliserVendor);
                String pk_vendor = String.valueOf(UUIDLong.longUUID());

                pst.setString(1, pk_vendor);
                pst.setString(2, providerImportFormBean.getProviderCode());
                pst.setString(3, providerImportFormBean.getProviderName());
                pst.setString(4, providerImportFormBean.getCompanyType());
                pst.setString(5, providerImportFormBean.getTaxRegister());
                pst.setString(6, providerImportFormBean.getProperty());
                pst.setString(7, providerImportFormBean.getQualitySystem());
                pst.setString(8, providerImportFormBean.getCvenbank());
                pst.setString(9, providerImportFormBean.getBankAccount());
                pst.setString(10, providerImportFormBean.getCmoneytype());
                pst.setString(11, providerImportFormBean.getCpurbillcode());
                pst.setString(12, providerImportFormBean.getCouttype());
                pst.setString(13, providerImportFormBean.getSupplyAddress());
                pst.setString(14, providerImportFormBean.getPostCode());
                pst.setString(15, providerImportFormBean.getLinkman());
                pst.setString(16, providerImportFormBean.getTel());
                pst.setString(17, providerImportFormBean.getFax());
                pst.setString(18, providerImportFormBean.getEmail());
                pst.setString(19, providerImportFormBean.getAddress());
                pst.setString(20, providerImportFormBean.getCcuspaycond());
                pst.setString(21, providerImportFormBean.getCcontent());
                pst.setString(22, MyUtil.getLimitLengthString(
                        providerImportFormBean.getSupplyCycle(), 20, "…"));
                pst.setString(23, uapUserID);
                pst.setString(24, MyUtil.getTodayDateStr());
                pst.setString(25, providerImportFormBean.getApplicationTime());
                pst.setString(26, providerImportFormBean.getMemberCode());
                pst.setString(27, providerImportFormBean.getcVenBankMemo());
                pst.setString(28, providerImportFormBean.getCvenfygl());

				// insert into fy_supplier_vendor
                // (pk_vendor,cvencode,cvenname,ctrade,cvenregcode,dorf,cqualitycond,cvenbank,cvenaccount,cmoneytype,cpurbillcode,couttype,cvenaddress,cvenpostcode,
                // cvenperson,
                // cvenphone,cvenfax,cvenemail,cveniaddress,ccuspaycond,ccontent,cycle,ccreateperson,dvendevdate,
                // ts,
                // creater,cVenBankMemo,cvenfygl,enterprise_type,registered_capital,establish_time,annual_sales,worker_number,premises,cvenlperson,legal_person_id_card
                // ) values
                // registered_capital VARCHAR2 (100) , --注册资金
                // establish_time VARCHAR2 (100) , --成立时间
                // annual_sales VARCHAR2 (100) , --年销售额
                // worker_number VARCHAR2 (100) , --职工总人数
                // premises VARCHAR2 (100) , --经营场所
                // legal_person_id_card VARCHAR2 (100) --法人代表身份证
                // pst.setString(29, providerImportFormBean.getQylb());
                // pst.setString(30, providerImportFormBean.getZczj());
                // pst.setString(31, providerImportFormBean.getClsj());
                // pst.setString(32, providerImportFormBean.getNxse());
                // pst.setString(33, providerImportFormBean.getZgrs());
                // pst.setString(34, providerImportFormBean.getJycs());
                // pst.setString(35, providerImportFormBean.getFrdb());
                // pst.setString(36, providerImportFormBean.getFrdbsfz());
                pst.executeUpdate();

                DBUtil.close(pst);

				// insert into fy_supplier_vendor_invclass (pk_veninv, pk_corp,
                // pk_invclass, cvendorclass, ts, pk_vendor, dvendevdate,
                // changeTrace) values ( ?, ?, ?, ?, ?, ?, ?, ?,
                // )
//                插入供货范围
                pst = conn.prepareStatement(insertVendorInvcalss);

				// "insert into fy_supplier_vendor_history
                // (pk_venhis, pk_vendor, pk_corp, pk_invclass,
                // changetype, changememo, changevouch, changecode,
                // changer, changedate) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
                // )";
//                日志
                pst1 = conn.prepareStatement(insertLog);
///遍历所有需要导入的公司
                for (int i = 0, size = this.companyList.size(); i < size; i++) {
                    String uapAccountID = this.companyList.get(i);

                    List<String> supplyRangeList = this.providerImportFormBean
                            .getSupplyRangeList();
//                    便利所有供货范围
                    for (int m = 0, mSize = supplyRangeList.size(); m < mSize; m++) {
                        String supplyRange = supplyRangeList.get(m);
                        System.out.println("888"+supplyRange);
                        if(supplyRange.equals("100200100000000004MZ")){
                        	supplyRange="10020010000000002FZB";
                        }
                        System.out.println("999"+supplyRange);
                        String pk_veninv = String.valueOf(UUIDLong.longUUID());

                        pst.setString(1, pk_veninv);
                        pst.setString(2, uapAccountID);
                        pst.setString(3, supplyRange);
//                        2标示位潜在
                        pst.setString(4, "2");
                        pst.setString(5, this.providerImportFormBean
                                .getApplicationTime());
                        pst.setString(6, pk_vendor);
                        pst.setString(7, MyUtil.getTodayDateStr());
                        pst.setLong(8, changeTrace);
                        pst.setString(9, providerImportFormBean.getMemberCode());

						// bankaccountname=?,bankcode = ?, bank =
                        // ?,bankaccount = ?,currency = ?
                        pst.setString(10,
                                providerImportFormBean.getBankAccountName());
                        pst.setString(11, providerImportFormBean.getBankCode());
                        pst.setString(12,
                                providerImportFormBean.getcVenBankMemo());
                        pst.setString(13,
                                providerImportFormBean.getBankAccount());
                        pst.setString(14,
                                providerImportFormBean.getCmoneytype());

                        pst.addBatch();

						// "insert into fy_supplier_vendor_history
                        // (pk_venhis, pk_vendor, pk_corp, pk_invclass,
                        // changetype, changememo, changevouch, changecode,
                        // changer, changedate) values ( ?, ?, ?, ?, ?, ?, ?, ?,
                        // ?, ?
                        // )";
                        writeLog(pst1, pk_vendor, uapAccountID, supplyRange,
                                "新增");
                    }
                }

                pst.executeBatch();
                pst1.executeBatch();
				// delApptitude(conn);
                // insertApptitude(conn, pk_vendor);
                success = true;
            } else if (providerIsExist == 1) {
                // 已存在的供应商 、变更供应商信息和供货范围
//            	变更主表信息
                pst = conn.prepareStatement(updateSuppliserVendor);
                pst.setString(1, providerImportFormBean.getProviderName());
                pst.setString(2, providerImportFormBean.getCompanyType());
                pst.setString(3, providerImportFormBean.getTaxRegister());
                pst.setString(4, providerImportFormBean.getProperty());
                pst.setString(5, providerImportFormBean.getQualitySystem());
                pst.setString(6, providerImportFormBean.getCvenbank());
                pst.setString(7, providerImportFormBean.getBankAccount());
                pst.setString(8, providerImportFormBean.getCmoneytype());
                pst.setString(9, providerImportFormBean.getCpurbillcode());
                pst.setString(10, providerImportFormBean.getCouttype());
                pst.setString(11, providerImportFormBean.getSupplyAddress());
                pst.setString(12, providerImportFormBean.getPostCode());
                pst.setString(13, providerImportFormBean.getLinkman());
                pst.setString(14, providerImportFormBean.getTel());
                pst.setString(15, providerImportFormBean.getFax());
                pst.setString(16, providerImportFormBean.getEmail());
                pst.setString(17, providerImportFormBean.getAddress());
                pst.setString(18, providerImportFormBean.getCcuspaycond());
                pst.setString(19, providerImportFormBean.getCcontent());
                pst.setString(20, MyUtil.getLimitLengthString(
                        providerImportFormBean.getSupplyCycle(), 20, "…"));
                pst.setString(21, uapUserID);
                pst.setString(22, MyUtil.getTodayDateStr());
                pst.setString(23, providerImportFormBean.getApplicationTime());
                pst.setString(24, providerImportFormBean.getcVenBankMemo());
                pst.setString(25, providerImportFormBean.getCvenfygl());

				// pst.setString(26, providerImportFormBean.getQylb());
                // pst.setString(27, providerImportFormBean.getZczj());
                // pst.setString(28, providerImportFormBean.getClsj());
                // pst.setString(29, providerImportFormBean.getNxse());
                // pst.setString(30, providerImportFormBean.getZgrs());
                // pst.setString(31, providerImportFormBean.getJycs());
                // pst.setString(32, providerImportFormBean.getFrdb());
                // pst.setString(33, providerImportFormBean.getFrdbsfz());
                pst.setString(26, providerImportFormBean.getProviderCode());

                pst.executeUpdate();

                DBUtil.close(pst);

//                查找供应商的所有供货范围
                pst = conn.prepareStatement(SELECT_VENDOR_INVCLASS);

                pst.setString(1, this.providerImportFormBean.getProviderCode());

                rs = pst.executeQuery();

                String pk_vendor = null;
                List<SupplyTypeBean> supplyTypeBeans = new ArrayList<SupplyTypeBean>();

                while (rs.next()) {

                    if (pk_vendor == null) {
                        pk_vendor = rs.getString(1);
                    }
                    SupplyTypeBean bean = new SupplyTypeBean();
                    bean.setPk_corp(rs.getString(2));
                    bean.setPk_invclass(rs.getString(3));
                    bean.setCvendorclass(rs.getString(4));
                    bean.setPk_veninv(rs.getString(5));
                    bean.setDenddate(rs.getString(6));
                    supplyTypeBeans.add(bean);
                    bean = null;
                }
                DBUtil.close(rs, pst);

//                之前的查询供货范围没有获取到任何数据，所以需要重新通过供应商编码获取供应商ID
                if (pk_vendor == null) {
                    pst = conn
                            .prepareStatement("select v.pk_vendor from fy_supplier_vendor v where v.cvencode=?  and nvl(v.dr,0) = 0");
                    pst.setString(1, providerImportFormBean.getProviderCode());
                    rs = pst.executeQuery();
                    while (rs.next()) {
                        pk_vendor = rs.getString(1);
                    }
                }
                DBUtil.close(rs, pst);
				// "insert into fy_supplier_vendor_invclass
                // (pk_veninv, pk_corp, pk_invclass, cvendorclass, ts,
                // pk_vendor, dvendevdate,changeTrace ) values (?, ?, ?, ?, ?,
                // ?, ?, ?) where
                // pk_corp=? and pk_invclass=? and pk_vendor=? and cvendorclass
                // <> ?"
//                插入供应商的供货范围
                pst = conn.prepareStatement(insertVendorInvcalssIfNotBlacklist);

				// "insert into fy_supplier_vendor_history
                // (pk_venhis, pk_vendor, pk_corp, pk_invclass,
                // changetype, changememo, changevouch, changecode,
                // changer, changedate) values ( ?, ?, ?, ?, ?,
                // ?, ?, ?, ?, ?
                // )where not exist (select * from where pk_corp=? and
                // pk_invclass=? and pk_vendor=? and cvendorclass = ?)";
//                插入日志
                pst1 = conn.prepareStatement(insertLogIfNotBlacklist);
//更新供货范围状态
                pst2 = conn
                        .prepareStatement(UPDATE_VENDOR_INVCLASS_STATE_CHANGE);


                String operate = "0".equals(this.providerImportFormBean
                        .getOption()) ? "潜在" : "合格";
//                UAP的供货范围的状态标识，2为潜在，1为合格，3为黑名单
                String state = "0".equals(this.providerImportFormBean
                        .getOption()) ? "2" : "1";
//遍历表单数据中的所有公司
                for (int i = 0, size = this.companyList.size(); i < size; i++) {
                    String uapAccountID = this.companyList.get(i);

                    List<String> supplyRangeList = this.providerImportFormBean
                            .getSupplyRangeList();
//遍历表单数据中所有需要导入的供货范围
                    for (int m = 0, mSize = supplyRangeList.size(); m < mSize; m++) {
                        String supplyRange = supplyRangeList.get(m);
                        SupplyTypeBean supplyTypeBean = null;
//                        当需要导入的供货范围不存在在数据库中
                        if ((supplyTypeBean = typeIsExist(uapAccountID,
                                supplyRange, supplyTypeBeans)) == null) {
//                        	新增的供货范围只能是导入潜在的，否则狐狸
                            if ("0".equals(this.providerImportFormBean
                                    .getOption())) {
                                String pk_veninv = String.valueOf(UUIDLong
                                        .longUUID());

                                pst.setString(1, pk_veninv);
                                pst.setString(2, uapAccountID);
                                pst.setString(3, supplyRange);
                                pst.setString(4, state);
                                pst.setString(5, this.providerImportFormBean
                                        .getApplicationTime());
                                pst.setString(6, pk_vendor);
                                pst.setString(7, MyUtil.getTodayDateStr());
                                pst.setLong(8, changeTrace);
                                pst.setString(9,
                                        providerImportFormBean.getMemberCode());

								// bankaccountname=?,bankcode = ?, bank =
                                // ?,bankaccount = ?,currency = ?
                                pst.setString(10, providerImportFormBean
                                        .getBankAccountName());
                                pst.setString(11,
                                        providerImportFormBean.getBankCode());
                                pst.setString(12, providerImportFormBean
                                        .getcVenBankMemo());
                                pst.setString(13,
                                        providerImportFormBean.getBankAccount());
                                pst.setString(14,
                                        providerImportFormBean.getCmoneytype());

								// pk_corp=? and pk_invclass=? and pk_vendor=?
                                // and cvendorclass
                                // // <> ?"
                                pst.setString(15, uapAccountID);
                                pst.setString(16, supplyRange);
                                pst.setString(17, pk_vendor);

                                pst.addBatch();

                                writeLogIfPrividerExist(pst1, pk_vendor,
                                        uapAccountID, supplyRange, operate);
                            }

                        } else {
//                        	不是黑名单、状态有发生变更、停用日起为空，或者当前日起闭停用日起早
                            if (!"3".equals(supplyTypeBean.getCvendorclass())
                                    && (!state.equals(supplyTypeBean
                                            .getCvendorclass())
                                    && !"1".equals(supplyTypeBean
                                            .getCvendorclass()) || supplyTypeBean
                                    .getDenddate() != null
                                    && !"".equals(supplyTypeBean
                                            .getDenddate())
                                    && !MyUtil.later(supplyTypeBean
                                            .getDenddate(), "00:00:00",
                                            MyUtil.getTodayDateStr(),
                                            "00:00:00"))) {
//                            	更新供货范围状态
                                pst2.setString(1, state);
                                pst2.setLong(2, changeTrace);
                                pst2.setString(3, "");
								// bankaccountname=?,bankcode = ?, bank =
                                // ?,bankaccount = ?,currency = ?
                                pst2.setString(4, providerImportFormBean
                                        .getBankAccountName());
                                pst2.setString(5,
                                        providerImportFormBean.getBankCode());
                                pst2.setString(6, providerImportFormBean
                                        .getcVenBankMemo());
                                pst2.setString(7,
                                        providerImportFormBean.getBankAccount());
                                pst2.setString(8,
                                        providerImportFormBean.getCmoneytype());
                                pst2.setString(9, supplyTypeBean.getPk_veninv());
                                pst2.addBatch();
//写入日志
                                writeLogIfPrividerExist(pst1, pk_vendor,
                                        uapAccountID, supplyRange, operate);
                            } else {
                                if (unchangingRangeList == null) {
                                    unchangingRangeList = new ArrayList<RangeBean>();
                                }
                                unchangingRangeList.add(new RangeBean(
                                        uapAccountID, supplyRange));
                            }
                        }
                    }
                }
                pst.executeBatch();
                pst2.executeBatch();
                pst1.executeBatch();

            } else {
                System.out.println("无法确认供应商导入操作,供应商导入表单据编号："
                        + providerImportFormBean.getNumber());
                return false;
            }

            DBUtil.close(rs, pst);
//更新银行信息
            pst = conn.prepareStatement(updateSuppliserBankInfo);
			// update fy_supplier_vendor_invclass i set
            // i.bankaccountname=?,i.bankcode = ?, i.bank = ?,i.bankaccount =
            // ?,i.currency = ? where i.pk_vendor = ( select v.pk_vendor from
            // fy_supplier_vendor v where v.cvencode = ? and nvl(v.dr,0) = 0)
            for (int i = 0, size = this.companyList.size(); i < size; i++) {
                String uapAccountID = this.companyList.get(i);
                pst.setString(1, providerImportFormBean.getBankAccountName());
                pst.setString(2, providerImportFormBean.getBankCode());
                pst.setString(3, providerImportFormBean.getcVenBankMemo());
                pst.setString(4, providerImportFormBean.getBankAccount());
                pst.setString(5, providerImportFormBean.getCmoneytype());
                pst.setString(6, providerImportFormBean.getProviderCode());
                pst.setString(7, uapAccountID);
                pst.addBatch();
            }
            pst.executeBatch();
            DBUtil.close(pst);

			// ERP删除
            // pst = conn.prepareStatement(ERPDELETESQL);
            // pst.setString(1, providerImportFormBean.getProviderCode());
            // pst.execute();
            // ERP更新
            pst = conn.prepareStatement(ERPUADATE);
            pst.setString(1, providerImportFormBean.getProviderCode());
            pst.setString(2, providerImportFormBean.getProviderCode());
            pst.setString(3, providerImportFormBean.getProviderCode());

            pst.execute();

            // ERP插入
            pst = conn.prepareStatement(ERPINSERTSQL);
            pst.setString(1, providerImportFormBean.getProviderCode());
            pst.execute();
            conn.commit();
            DBUtil.setAutoCommit(conn, true);
            writeToErpSuccess = success = true;
            pst = conn
                    .prepareStatement("select i.pk_corp, i.pk_invclass, c.unitname, si.cinvclasscode, v.cvencode, i.cvendorclass, v.cvenname from fy_supplier_vendor_invclass i left join bd_corp c on i.pk_corp = c.pk_corp left join fy_supplier_invclass si on si.pk_invclass = i.pk_invclass left join fy_supplier_vendor v on v.pk_vendor = i.pk_vendor where i.changeTrace= ?");
            pst.setLong(1, changeTrace);
            rs = pst.executeQuery();
            while (rs.next()) {
				// select i.pk_corp, i.pk_invclass, c.unitname,
                // si.cinvclasscode, v.cvencode, i.cvendorclass
                if (successRangeList == null) {
                    successRangeList = new ArrayList<RangeBean>();
                }
                successRangeList.add(new RangeBean(rs.getString(1), rs
                        .getString(2), rs.getString(3), rs.getString(5), rs
                        .getString(4), rs.getString(6), rs.getString(7)));
            }
            setFailRangeList();

            writeAptitudeSuccess = true;
			// AptitudeDao.writeAptitude(conn,
            // apptitudeBeans, providerImportFormBean.getProviderCode(),
            // "供应商导入" + providerImportFormBean.getNumber(),
            // providerImportFormBean.getMemberCode());
            System.out.println("success");
        } catch (Exception e) {
            success = false;
            DBUtil.rollback(conn);
            e.printStackTrace();
            System.out.println("供应商导入审批数据导入UPA异常");
        } finally {
            DBUtil.setAutoCommit(conn, true);
            DBUtil.close(pst2);
            DBUtil.close(pst1);
            DBUtil.close(rs, pst, conn);
        }

		// if (success)
        // writeToErp();
        return success;
    }

    public boolean isWriteAptitudeSuccess() {
        return writeAptitudeSuccess;
    }

    /**
     * 写入到ERP数据库
     */
	// private void writeToErp() {
    // if (successRangeList != null) {
    // Connection conn = null;
    // PreparedStatement pst = null;
    // try {
    //
    // // FileOutputStream fos = new FileOutputStream("E:/1111");
    // // fos.write(providerImportFormBean.printFields().getBytes());
    // // fos.flush();
    // // fos.close();
    //
    // conn = DBUtil.connect(ERP_OAConstant.ERPDriverClassName,
    // ERP_OAConstant.ERPURL, ERP_OAConstant.ERPUserName,
    // ERP_OAConstant.ERPPassword);
    //
    // DBUtil.setAutoCommit(conn, false);
    //
    // pst = conn
    // .prepareStatement("update apps.FYG_PO_UAP_VENDORS set ZT=? where VENDORS_NUM=? and SYB=? and UAPLB=?");
    // // update apps.FYG_PO_UAP_VENDORS set ZT=? where VENDORS_NUM=?
    // // and
    // // SY=? and UAPLB=?
    // RangeBean range = null;
    // for (int i = 0, size = successRangeList.size(); i < size; i++) {
    // range = successRangeList.get(i);
    // pst.setString(1, "2".equals(range.getCvendorclass()) ? "潜在"
    // : "合格");
    // pst.setString(2, range.getProviderCode());
    // pst.setString(3, range.getCorpName());
    // pst.setString(4, range.getInvclassCode());
    // pst.addBatch();
    // range = null;
    // }
    //
    // pst.executeBatch();
    // DBUtil.close(pst);
    // // insert into fy_supplier_vendor_invclass (pk_veninv, pk_corp,
    // // pk_invclass, cvendorclass, ts, pk_vendor, dvendevdate,
    // // changeTrace, creater) select ?, ?, ?, ?, ?, ?, ?, ?, ? from
    // // dual
    // // where not exists ( select * from fy_supplier_vendor_invclass
    // // where pk_corp=? and pk_invclass=? and pk_vendor=? and (dr='0'
    // // or
    // // dr is null) and ( cvendorclass = '3' or cvendorclass = '0'))
    // pst = conn
    // .prepareStatement("insert into apps.FYG_PO_UAP_VENDORS (VENDORS_NUM, VENDORS_NAME, SYB, UAPLB, ZT) select  ?, ?, ?, ?, ? from dual where not exists  ( select * from apps.FYG_PO_UAP_VENDORS f where f.VENDORS_NUM=? and f.SYB=? and f.UAPLB=?)");
    // // insert into apps.FYG_PO_UAP_VENDORS (VENDORS_NUM 供应商编号,
    // // VENDORS_NAME 供应商名称, SYB 事业部, UAPLB UAP类别, ZT 状态) select ?, ?,
    // // ?,
    // // ?, ?, ?, ?, ?, ? from dual where not exists ( select * from
    // // apps.FYG_PO_UAP_VENDORS f where f.VENDORS_NUM=? and f.SY=?
    // // and
    // // f.UAPLB=?)");
    //
    // for (int i = 0, size = successRangeList.size(); i < size; i++) {
    // range = successRangeList.get(i);
    //
    // pst.setString(1, range.getProviderCode());
    // pst.setString(2, range.getProviderName());
    // pst.setString(3, range.getCorpName());
    // pst.setString(4, range.getInvclassCode());
    // pst.setString(5, "2".equals(range.getCvendorclass()) ? "潜在"
    // : "合格");
    // pst.setString(6, range.getProviderCode());
    // pst.setString(7, range.getCorpName());
    // pst.setString(8, range.getInvclassCode());
    // pst.addBatch();
    // range = null;
    // }
    // pst.executeBatch();
    //
    // conn.commit();
    //
    // writeToErpSuccess = true;
    //
    // } catch (Exception e) {
    // DBUtil.rollback(conn);
    // DBUtil.setAutoCommit(conn, true);
    // e.printStackTrace();
    // log.error(
    // "供应商导入写入ERP数据库异常,单据编号： "
    // + providerImportFormBean.getNumber(), e);
    // writeToErpSuccess = false;
    // } finally {
    // DBUtil.setAutoCommit(conn, true);
    // DBUtil.close(pst, conn);
    // }
    // } else {
    // log.warn("供应商导入写入ERP数据库数据为空,单据编号： "
    // + providerImportFormBean.getNumber());
    //
    // }
    // }
    private void setFailRangeList() {
        if (successRangeList == null
                || successRangeList.size() < this.companyList.size()
                * this.providerImportFormBean.getSupplyRangeList()
                .size()) {
            for (int i = 0, size = this.companyList.size(); i < size; i++) {
                String uapAccountID = this.companyList.get(i);
                List<String> supplyRangeList = this.providerImportFormBean
                        .getSupplyRangeList();
                for (int m = 0, mSize = supplyRangeList.size(); m < mSize; m++) {

                    String supplyRange = supplyRangeList.get(m);
              System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"+supplyRange);
                    boolean fail = true;
                    if (successRangeList != null) {
                        for (int o = 0, sSize = successRangeList.size(); o < sSize; o++) {
                            RangeBean bean = successRangeList.get(o);
                            if (uapAccountID.equals(bean.getPk_corp())
                                    && supplyRange
                                    .equals(bean.getPk_invclass())) {
                                fail = false;
                                break;
                            }
                            bean = null;
                        }
                    }
                    if (fail && unchangingRangeList != null) {
                        for (int o = 0, sSize = unchangingRangeList.size(); o < sSize; o++) {
                            RangeBean bean = unchangingRangeList.get(o);
                            if (uapAccountID.equals(bean.getPk_corp())
                                    && supplyRange
                                    .equals(bean.getPk_invclass())) {
                                fail = false;
                                break;
                            }
                            bean = null;
                        }
                    }

                    if (fail) {
                        if (failRangeList == null) {
                            failRangeList = new ArrayList<RangeBean>();
                        }
                        failRangeList.add(new RangeBean(uapAccountID,
                                supplyRange));
                    }
                }
            }
        }
    }

    private void writeLog(PreparedStatement pst1, String pk_vendor,
            String uapAccountID, String supplyRange, String operate)
            throws SQLException {
        pst1.setString(1, String.valueOf(UUIDLong.longUUID()));
        pst1.setString(2, pk_vendor);
        pst1.setString(3, uapAccountID);
        pst1.setString(4, supplyRange);
        pst1.setString(5, operate);
        pst1.setString(6, this.providerImportFormBean.getReason());
        pst1.setString(7, "OA供应商导入批准表");
        pst1.setString(8, this.providerImportFormBean.getNumber());
        pst1.setString(9, uapUserID);
        pst1.setString(10, MyUtil.getNowStr());
        pst1.setString(11, MyUtil.getTodayDateStr());
        pst1.setString(12, this.providerImportFormBean.getMemberCode());
        pst1.addBatch();
    }

    private void writeLogIfPrividerExist(PreparedStatement pst1,
            String pk_vendor, String uapAccountID, String supplyRange,
            String operate) throws SQLException {
		// insert into fy_supplier_vendor_history(pk_venhis, pk_vendor, pk_corp,
        // pk_invclass, changetype, changememo, changevouch, changecode,
        // changer, ts, changedate) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
        // where not exist (select * from where pk_corp=? and pk_invclass=? and
        // pk_vendor=? and cvendorclass <> '3')

        pst1.setString(1, String.valueOf(UUIDLong.longUUID()));
        pst1.setString(2, pk_vendor);
        pst1.setString(3, uapAccountID);
        pst1.setString(4, supplyRange);
        pst1.setString(5, operate);
        pst1.setString(6, this.providerImportFormBean.getReason());
        pst1.setString(7, "OA供应商导入批准表");
        pst1.setString(8, this.providerImportFormBean.getNumber());
        pst1.setString(9, uapUserID);
        pst1.setString(10, MyUtil.getNowStr());
        pst1.setString(11, MyUtil.getTodayDateStr());
        pst1.setString(12, providerImportFormBean.getMemberCode());
        pst1.setString(13, uapAccountID);
        pst1.setString(14, supplyRange);
        pst1.setString(15, pk_vendor);
        pst1.addBatch();
    }

    public boolean setUapAccountList() {
        this.companyList = this.providerImportFormBean.getCompanyIDList();
        return this.companyList != null && this.companyList.size() > 0;
    }

    private SupplyTypeBean typeIsExist(String pk_corp, String pk_invclass,
            List<SupplyTypeBean> supplyTypeBeans) {
        if (supplyTypeBeans != null) {
            for (int i = 0, size = supplyTypeBeans.size(); i < size; i++) {
                if (supplyTypeBeans.get(i) != null) {
                    if (pk_corp.equals(supplyTypeBeans.get(i).getPk_corp())
                            && pk_invclass.equals(supplyTypeBeans.get(i)
                                    .getPk_invclass())) {
                        return supplyTypeBeans.get(i);
                    }
                }
            }
        }
        return null;
    }

    public boolean isWriteToErpSuccess() {
        return writeToErpSuccess;
    }
    
	//写入MDM数据库
	public boolean getStateReturn() {
//		System.err.println("123");
		try {
			
//			for (int i = 0, size = beans.size(); i < size; i++) {
				IServiceRequester Requester = BaseServiceRequester.getInstance();
				List<GroupRecord> grs1 = new ArrayList<GroupRecord>();//多层用
				// 定义请求报文对象
				IMsgObject reqMo = new MsgObject(IMsgObject.MOType.initSR);
				String SourceSysID = "06004"; // 请求方(发布方)
				String ServiceID = "06001000000039";// 响应方(发布方)
				// 获取系统时间
				Date date = new Date();
				SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
				String format = simpleDateFormat.format(date);
				// 对请求报文对象进行设置
				reqMo.setSourceSysID(SourceSysID); // 设置报文 请求方系统号
				reqMo.setSerialNO(format.substring(0, 8) + SourceSysID
						+ String.format("%06d", ++MaterialRequestNoTimerTask.requestNo));// 设置报文流水号,第三段表示今天发送的第几的一次报文
				reqMo.setServiceID(ServiceID); // 设置报文 提供方服务号
				reqMo.setServiceDateTime(format); // 设置报文 日期时间
				
				reqMo.setReqValue("BOID","MDM_EQUIPMENT_F" );
				GroupRecord gr1 = new GroupRecord();
				gr1.setName("ROW");
				gr1.setFieldValue("MDM_CODE",providerImportFormBean.getProviderCode());//供应商编码
				gr1.setFieldValue("Name",providerImportFormBean.getProviderName());//供应商名称
				gr1.setFieldValue("UpdateTime", format);//修改时间
				grs1.add(gr1);
				reqMo.setReqGroupRecord(grs1);
//				System.err.println(grs1);
				log.info("reqMo:"+reqMo);
//				System.err.println(reqMo);
				IMsgObject respMo = null;
				respMo = Requester.execute(reqMo);//发布报文
//				System.err.println(respMo);
//				log.info("respMo:"+respMo);
//			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return true;
		
	}

}

class SupplyTypeBean {

    private String pk_corp;
    private String pk_invclass;
    private String cvendorclass;
    private String pk_veninv;
    private String denddate;

    public String getDenddate() {
        return denddate;
    }

    public void setDenddate(String denddate) {
        this.denddate = denddate;
    }

    public String getPk_veninv() {
        return pk_veninv;
    }

    public void setPk_veninv(String pk_veninv) {
        this.pk_veninv = pk_veninv;
    }

    public String getPk_corp() {
        return pk_corp;
    }

    public void setPk_corp(String pk_corp) {
        this.pk_corp = pk_corp;
    }

    public String getPk_invclass() {
        return pk_invclass;
    }

    public void setPk_invclass(String pk_invclass) {
        this.pk_invclass = pk_invclass;
    }

    public void setCvendorclass(String cvendorclass) {
        this.cvendorclass = cvendorclass;
    }

    public String getCvendorclass() {
        return cvendorclass;
    }

}
